
CCMS DATABASE
Campus Club Management System

 1. Create Database
DROP DATABASE IF EXISTS ccms;
CREATE DATABASE ccms CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE ccms;

 2. Core Tables

2.1 STUDENT
CREATE TABLE Student (
    StudentID      INT AUTO_INCREMENT PRIMARY KEY,
    StudentNumber  VARCHAR(20) NOT NULL UNIQUE,
    FirstName      VARCHAR(50) NOT NULL,
    LastName       VARCHAR(50) NOT NULL,
    Email          VARCHAR(100) NOT NULL UNIQUE,
    JoinDate       DATE NOT NULL,
    Status         ENUM('Active','Inactive') NOT NULL DEFAULT 'Active',
    CreatedAt      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

2.2 CLUB
CREATE TABLE Club (
    ClubID        INT AUTO_INCREMENT PRIMARY KEY,
    ClubName      VARCHAR(100) NOT NULL UNIQUE,
    Category      ENUM('Academic','Sports','Cultural','SpecialInterest','Other') NOT NULL,
    FoundingDate  DATE NOT NULL,
    Description   TEXT,
    IsActive      TINYINT(1) NOT NULL DEFAULT 1,
    CreatedAt     TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

2.3 MEMBERSHIP (Student <-> Club) (Many-to-Many)
CREATE TABLE Membership (
    MembershipID  INT AUTO_INCREMENT PRIMARY KEY,
    StudentID     INT NOT NULL,
    ClubID        INT NOT NULL,
    JoinedDate    DATE NOT NULL,
    Role          ENUM('Member','Committee','President','Treasurer','Secretary') NOT NULL DEFAULT 'Member',
    Status        ENUM('Active','Suspended','Cancelled') NOT NULL DEFAULT 'Active',
    CONSTRAINT fk_membership_student
        FOREIGN KEY (StudentID) REFERENCES Student(StudentID)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_membership_club
        FOREIGN KEY (ClubID) REFERENCES Club(ClubID)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT uq_membership_student_club UNIQUE (StudentID, ClubID)
) ENGINE=InnoDB;

2.4 EVENT (per Club)
CREATE TABLE Event (
    EventID      INT AUTO_INCREMENT PRIMARY KEY,
    ClubID       INT NOT NULL,
    EventName    VARCHAR(150) NOT NULL,
    EventDate    DATETIME NOT NULL,
    Venue        VARCHAR(100) NOT NULL,
    Capacity     INT NOT NULL,
    IsMandatory  TINYINT(1) NOT NULL DEFAULT 0,
    CreatedAt    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_event_club
        FOREIGN KEY (ClubID) REFERENCES Club(ClubID)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT chk_event_capacity CHECK (Capacity > 0)
) ENGINE=InnoDB;

2.5 PARTICIPATION (Student at Event)
CREATE TABLE Participation (
    ParticipationID INT AUTO_INCREMENT PRIMARY KEY,
    EventID         INT NOT NULL,
    StudentID       INT NOT NULL,
    RegisteredAt    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    Attended        TINYINT(1) NOT NULL DEFAULT 0,
    CONSTRAINT fk_participation_event
        FOREIGN KEY (EventID) REFERENCES Event(EventID)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_participation_student
        FOREIGN KEY (StudentID) REFERENCES Student(StudentID)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT uq_event_student UNIQUE (EventID, StudentID)
) ENGINE=InnoDB;

2.6 ANNOUNCEMENT (per Club)
CREATE TABLE Announcement (
    AnnouncementID  INT AUTO_INCREMENT PRIMARY KEY,
    ClubID          INT NOT NULL,
    Title           VARCHAR(150) NOT NULL,
    Message         TEXT NOT NULL,
    CreatedAt       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CreatedBy       VARCHAR(100) NOT NULL,
    CONSTRAINT fk_announcement_club
        FOREIGN KEY (ClubID) REFERENCES Club(ClubID)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

2.7 COMMUNICATION LOG (Announcement sent to Student)
CREATE TABLE CommunicationLog (
    LogID           INT AUTO_INCREMENT PRIMARY KEY,
    AnnouncementID  INT NOT NULL,
    StudentID       INT NOT NULL,
    SentAt          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    DeliveryStatus  ENUM('Sent','Failed','Opened') NOT NULL DEFAULT 'Sent',
    CONSTRAINT fk_log_announcement
        FOREIGN KEY (AnnouncementID) REFERENCES Announcement(AnnouncementID)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_log_student
        FOREIGN KEY (StudentID) REFERENCES Student(StudentID)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

2.8 MEMBERSHIP AUDIT TABLE
CREATE TABLE AuditMembership (
    AuditID       INT AUTO_INCREMENT PRIMARY KEY,
    MembershipID  INT NULL,
    StudentID     INT NULL,
    ClubID        INT NULL,
    Action        ENUM('INSERT','UPDATE','DELETE') NOT NULL,
    ActionTime    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PerformedBy   VARCHAR(100) NULL,
    OldStatus     VARCHAR(20) NULL,
    NewStatus     VARCHAR(20) NULL
) ENGINE=InnoDB;

2.9 USER TABLE (for PHP admin login)
CREATE TABLE UserAccount (
    UserID       INT AUTO_INCREMENT PRIMARY KEY,
    Username     VARCHAR(50) NOT NULL UNIQUE,
    PasswordHash VARCHAR(255) NOT NULL,
    Role         ENUM('Admin','Staff') NOT NULL DEFAULT 'Admin',
    CreatedAt    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

3. Indexes for Performance
CREATE INDEX idx_membership_student ON Membership(StudentID);
CREATE INDEX idx_membership_club ON Membership(ClubID);
CREATE INDEX idx_event_clubdate ON Event(ClubID, EventDate);
CREATE INDEX idx_participation_event ON Participation(EventID);
CREATE INDEX idx_participation_student ON Participation(StudentID);
CREATE INDEX idx_announcement_club ON Announcement(ClubID);
CREATE INDEX idx_log_announcement ON CommunicationLog(AnnouncementID);

4. Sample Data
4.1 Students
INSERT INTO Student (StudentNumber, FirstName, LastName, Email, JoinDate)
VALUES
('S1001','Alice','Stewart','alice.stewart@example.ac.uk','2025-09-01'),
('S1002','Mike','Taylor','mike.taylor@example.ac.uk','2025-09-01'),
('S1003','John','Brown','john.brown@example.ac.uk','2025-09-02'),
('S1004','George','William','george.william@example.ac.uk','2025-09-02'),
('S1005','Emilly','White','emilly.white@example.ac.uk','2025-09-03');4.2 Clubs
INSERT INTO Club (ClubName, Category, FoundingDate, Description)
VALUES
('Computer Science Society','Academic','2015-10-01','Talks, hackathons and coding meetups.'),
('Football Club','Sports','2010-09-15','Competitive and recreational football.'),
('International Students Association','Cultural','2012-01-10','Cultural exchange and social events.'),
('E-Sports Club','SpecialInterest','2019-03-05','Competitive gaming and tournaments.');

4.3 Memberships
INSERT INTO Membership (StudentID, ClubID, JoinedDate, Role)
VALUES
(1,1,'2025-09-05','Member'),
(2,1,'2025-09-05','Committee'),
(3,2,'2025-09-06','Member'),
(4,3,'2025-09-06','Member'),
(5,4,'2025-09-07','Member'),
(1,3,'2025-09-08','Member');

4.4 Events
INSERT INTO Event (ClubID, EventName, EventDate, Venue, Capacity, IsMandatory)
VALUES
(1,'Intro to CC Society','2025-10-10 18:00:00','Room CS101',50,0),
(1,'Hackathon 24h','2025-11-01 09:00:00','Innovation Lab',80,0),
(2,'Football Trials','2025-09-20 16:00:00','Sports Ground A',40,0),
(3,'Welcome Social','2025-09-25 18:30:00','Student Union Bar',100,0),
(4,'E-Sports LAN Party','2025-10-05 17:00:00','Gaming Arena',30,0);

4.5 Participation
INSERT INTO Participation (EventID, StudentID, Attended)
VALUES
(1,1,1), (1,2,1), (1,3,0),
(3,3,1), (3,4,1),
(4,1,0), (4,5,1);

4.6 Announcements
INSERT INTO Announcement (ClubID, Title, Message, CreatedBy)
VALUES
(1,'Welcome to CS Society','First meeting will be on 10th October.','cs.pres@example.ac.uk'),
(2,'Football Trials','Trials schedule is now published.','coach@example.ac.uk'),
(3,'Cultural Evening','Join us for food and music from around the world.','isa.chair@example.ac.uk');

4.7 Communication Log
INSERT INTO CommunicationLog (AnnouncementID, StudentID, DeliveryStatus)
VALUES
(1,1,'Opened'),
(1,2,'Sent'),
(2,3,'Sent'),
(3,4,'Opened'),
(3,1,'Sent');

4.8 User Accounts (admin password needs to be hashed in PHP)
INSERT INTO UserAccount (Username, PasswordHash, Role)
VALUES
('admin', '$2y$10$/Jt4tXS29jYSr3dQTevxmumacL0GyRtHam9eVGNTjbBsLYcyzON/y', 'Admin');

5. Triggers for Auditing
DELIMITER $$

Audit INSERT on Membership
CREATE TRIGGER trg_membership_insert
AFTER INSERT ON Membership
FOR EACH ROW
BEGIN
    INSERT INTO AuditMembership (MembershipID, StudentID, ClubID, Action, OldStatus, NewStatus, PerformedBy)
    VALUES (NEW.MembershipID, NEW.StudentID, NEW.ClubID, 'INSERT', NULL, NEW.Status, 'system');
END$$

Audit UPDATE on Membership
CREATE TRIGGER trg_membership_update
AFTER UPDATE ON Membership
FOR EACH ROW
BEGIN
    IF OLD.Status <> NEW.Status THEN
        INSERT INTO AuditMembership (MembershipID, StudentID, ClubID, Action, OldStatus, NewStatus, PerformedBy)
        VALUES (NEW.MembershipID, NEW.StudentID, NEW.ClubID, 'UPDATE', OLD.Status, NEW.Status, 'system');
    END IF;
END$$

Audit DELETE on Membership
CREATE TRIGGER trg_membership_delete
AFTER DELETE ON Membership
FOR EACH ROW
BEGIN
    INSERT INTO AuditMembership (MembershipID, StudentID, ClubID, Action, OldStatus, NewStatus, PerformedBy)
    VALUES (OLD.MembershipID, OLD.StudentID, OLD.ClubID, 'DELETE', OLD.Status, NULL, 'system');
END$$

DELIMITER ;

6. Stored Procedures with Transactions 
DELIMITER $$

6.1 AddMembership with Transaction
CREATE PROCEDURE sp_AddMembership (
    IN pStudentID INT,
    IN pClubID INT,
    IN pRole VARCHAR(20),
    IN pPerformedBy VARCHAR(100)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
    END;

    START TRANSACTION;

    INSERT INTO Membership (StudentID, ClubID, JoinedDate, Role)
    VALUES (pStudentID, pClubID, CURDATE(), pRole);

    COMMIT;
END$$

 6.2 Bulk register all active members of a club to an event
CREATE PROCEDURE sp_BulkRegisterForEvent (
    IN pEventID INT,
    IN pClubID INT
)
BEGIN
    DECLARE vCapacity INT;
    DECLARE vCurrentCount INT;
    DECLARE vMembersCount INT;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
    END;

    SELECT Capacity INTO vCapacity FROM Event WHERE EventID = pEventID;

    SELECT COUNT(*) INTO vCurrentCount FROM Participation WHERE EventID = pEventID;

    SELECT COUNT(*) INTO vMembersCount
    FROM Membership
    WHERE ClubID = pClubID AND Status = 'Active';

   
    IF vCapacity IS NOT NULL AND vCurrentCount + vMembersCount <= vCapacity THEN
        START TRANSACTION;

        INSERT INTO Participation (EventID, StudentID, Attended)
        SELECT pEventID, m.StudentID, 0
        FROM Membership m
        WHERE m.ClubID = pClubID AND m.Status = 'Active'
          AND NOT EXISTS (
              SELECT 1 FROM Participation p
              WHERE p.EventID = pEventID AND p.StudentID = m.StudentID
          );

        COMMIT;
    END IF;
END$$

6.3 Delete Club safely with transaction
CREATE PROCEDURE sp_DeleteClub (
    IN pClubID INT
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
    END;

    START TRANSACTION;

         DELETE FROM Club WHERE ClubID = pClubID;

    COMMIT;
END$$

DELIMITER ;

Example Test Queries

List all clubs and their active member counts
SELECT c.ClubName, COUNT(m.MembershipID) AS ActiveMembers
FROM Club c
LEFT JOIN Membership m ON c.ClubID = m.ClubID AND m.Status = 'Active'
GROUP BY c.ClubID, c.ClubName
ORDER BY ActiveMembers DESC;

List participation for a specific event
SELECT e.EventName, s.FirstName, s.LastName, p.Attended
FROM Participation p
JOIN Event e ON p.EventID = e.EventID
JOIN Student s ON p.StudentID = s.StudentID
WHERE e.EventID = 1;

Call stored procedure (run these manually for screenshots)
CALL sp_AddMembership(2, 3, 'Member', 'admin');
CALL sp_BulkRegisterForEvent(2, 1);
CALL sp_DeleteClub(4);
